<?php

  function getWebpageId($url) {
    $query = "SELECT id FROM WebPage WHERE url = '" . $url . "'";
    $result = mysql_query($query);
    $webpage_id = -1;
    if (mysql_num_rows($result) == 0) {
      $webpage_id = registerPage($url);
    } else {
      $result_row = mysql_fetch_assoc($result);
      $webpage_id = $result_row['id'];
    }
    return $webpage_id;
  }

  function addLink($from_url, $from_url_param, $to_url, $to_url_param, $note) {
    $return_string = "";

    if (!isset($_SESSION['user_name'])) {
      $return_string .= "<returncode>0</returncode>\n";
      $return_string .= "<errormessage>You're not logged in!</errormessage>\n";
      return $return_string;
    }

    $from_webpage_id = getWebpageId($from_url);
    $to_webpage_id = getWebpageId($to_url);
    $from_webpagequery_id = 
        getPageParameterId($from_webpage_id, $from_url_param);
    $to_webpagequery_id = getPageParameterId($to_webpage_id, $to_url_param);

    $user_id = $_SESSION['user_id'];
    $query = "SELECT * FROM Link WHERE user_id=$user_id "
           . "AND from_webpagequery_id=$from_webpagequery_id "
           . "AND to_webpagequery_id=$to_webpagequery_id";
    $result = mysql_query($query);
      
    if (mysql_num_rows($result) == 0) {
      $query2 = "INSERT INTO Link (user_id, from_webpagequery_id, "
              . "to_webpagequery_id, comment) "
              . "VALUES($user_id, $from_webpagequery_id, "
              . "$to_webpagequery_id, '$note')";
      $result = mysql_query($query2);
      $result = mysql_query($query);
    }
    $result_row = mysql_fetch_assoc($result);
    $return_string .= "<returncode>1</returncode>";

    return $return_string;
  }

  function deleteLink($from_url, $from_url_param, $to_url, $to_url_param) {
    $return_string = "";

    if (!isset($_SESSION['user_name'])) {
      $return_string .= "<returncode>0</returncode>\n";
      $return_string .= "<errormessage>You're not logged in!</errormessage>\n";
      return $return_string;
    }

    $from_webpage_id = getWebpageId($from_url);
    $to_webpage_id = getWebpageId($to_url);
    $from_webpagequery_id = 
        getPageParameterId($from_webpage_id, $from_url_param);
    $to_webpagequery_id = getPageParameterId($to_webpage_id, $to_url_param);

    $user_id = $_SESSION['user_id'];
    $query = "DELETE FROM Link WHERE user_id=$user_id "
           . "AND from_webpagequery_id=$from_webpagequery_id "
           . "AND to_webpagequery_id=$to_webpagequery_id";
    $result = mysql_query($query);
      
    $return_string .= "<returncode>1</returncode>";

    return $return_string;
  }

  function getLinks($url, $url_param) {
    $return_string = "";

    $webpage_id = getWebpageId($url);
    $webpagequery_id = getPageParameterId($webpage_id, $url_param);

    $return_string .= "<all_links>";
    $return_string .= "<link_from>";
    $query = "SELECT to_webpagequery_id, from_webpagequery_id, "
           . "COUNT(user_id) AS user_num "
           . "FROM Link WHERE from_webpagequery_id=$webpagequery_id "
           . "GROUP BY to_webpagequery_id";
    $result = mysql_query($query);
    $return_string .= getLinksXMLDescription($result, true);
    $return_string .= "</link_from>";

    $return_string .= "<link_to>";
    $query = "SELECT to_webpagequery_id, from_webpagequery_id, "
           . "COUNT(user_id) AS user_num "
           . "FROM Link WHERE to_webpagequery_id=$webpagequery_id "
           . "GROUP BY from_webpagequery_id";
    $result = mysql_query($query);
    $return_string .= getLinksXMLDescription($result, false);
    $return_string .= "</link_to>";
    $return_string .= "</all_links>";

    if (isset($_SESSION['user_id'])) {
      $return_string .= "<user_links>";
      $return_string .= "<link_from>";
      $query = "SELECT to_webpagequery_id, from_webpagequery_id, comment,"
             . "COUNT(user_id) AS user_num "
             . "FROM Link WHERE from_webpagequery_id=$webpagequery_id "
             . "AND user_id=" . $_SESSION['user_id'] . " "
             . "GROUP BY to_webpagequery_id";
      $result = mysql_query($query);
      $return_string .= getLinksXMLDescription($result, true, true);
      $return_string .= "</link_from>";

      $return_string .= "<link_to>";
      $query = "SELECT to_webpagequery_id, from_webpagequery_id, comment,"
             . "COUNT(user_id) AS user_num "
             . "FROM Link WHERE to_webpagequery_id=$webpagequery_id "
             . "AND user_id=" . $_SESSION['user_id'] . " "
             . "GROUP BY from_webpagequery_id";
      $result = mysql_query($query);
      $return_string .= getLinksXMLDescription($result, false, true);
      $return_string .= "</link_to>";
      $return_string .= "</user_links>";
    }
    return $return_string;
  }

  function getLinksXMLDescription($links, $from, $own=false) {
    $return_string = "";

    while($link = mysql_fetch_assoc($links)) {
      $return_string .= "<link>";

      $return_string .= "<user_num>" . $link['user_num'] . "</user_num>";
      if ($own) {
        $return_string .= "<comment>" . htmlentities($link['comment']) . "</comment>";
      } else if (isset($_SESSION['user_id'])) {
        $query = "SELECT * from Link"
               . " WHERE to_webpagequery_id=" . $link['to_webpagequery_id']
               . " AND from_webpagequery_id=" . $link['from_webpagequery_id']
               . " AND user_id=" . $_SESSION['user_id'];
        $result = mysql_query($query);
        
        $return_string .= mysql_num_rows($result) > 0 ? "<own>1</own>" :
            "<own>0</own>";
      }
      $webpagequery_id = $from ? $link['to_webpagequery_id'] :
                                 $link['from_webpagequery_id'];
      $query = "SELECT (ROUND(AVG(rating),2)) AS avgRating FROM WebPageRating"
             . " WHERE webpagequery_id=$webpagequery_id";
      $result = mysql_query($query);
      $result_row = mysql_fetch_assoc($result);
      $return_string .= "<page_rating>" . $result_row['avgRating'] 
                      . "</page_rating>";
 
      $query = "SELECT * FROM WebPageQuery WHERE id=$webpagequery_id";
      $result = mysql_query($query);
      $result_row = mysql_fetch_assoc($result);
      $title = $result_row['title'];
      $return_string .= "<url_param>" . htmlentities($result_row['query']) 
                      . "</url_param>";

      $query = "SELECT * FROM WebPage WHERE id=" . $result_row['webpage_id'];
      $result = mysql_query($query);
      $result_row = mysql_fetch_assoc($result);
      $return_string .= "<url>" . htmlentities($result_row['url']) . "</url>";
      $return_string .= "<title>" 
                      . ($title == null ? htmlentities($result_row['url']) : $title)
                      . "</title>";
      
      $query = "SELECT SUM(rating) as totalRating FROM LinkRating "
             . "WHERE from_webpagequery_id=" . $link['from_webpagequery_id']
             . " AND to_webpagequery_id=" . $link['to_webpagequery_id'];
      $result = mysql_query($query);
      $result_row = mysql_fetch_assoc($result);
      $link_rating = $result_row['totalRating'] == null ? 0 
                     : $result_row['totalRating'];
      $return_string .= "<link_rating>" . $link_rating . "</link_rating>";

      $return_string .= "</link>";
    }

    return $return_string;
  }

  function getLinkComment($from_url, $from_url_param,
                          $to_url, $to_url_param) {
    $return_string = "";
    $from_webpage_id = getWebpageId($from_url);
    $from_webpagequery_id = getPageParameterId($from_webpage_id, $from_url_param);
    $to_webpage_id = getWebpageId($to_url);
    $to_webpagequery_id = getPageParameterId($to_webpage_id, $to_url_param);

    $query = "SELECT User.name, Link.comment "
           . "FROM Link JOIN User ON User.id=Link.user_id "
           . "WHERE Link.from_webpagequery_id=$from_webpagequery_id "
           . "AND Link.to_webpagequery_id=$to_webpagequery_id";

    $result = mysql_query($query);
    while($result_row = mysql_fetch_assoc($result)) {
      $return_string .= "<link_comment>"
                      . "<comment>" . htmlentities($result_row['comment']) . "</comment>"
                      . "<user>" . $result_row['name'] . "</user>"
                      . "</link_comment>";
    }
    return $return_string;
  }

  function rateLink($from_url, $from_url_param, 
                    $to_url, $to_url_param, $up) {
    $return_string = "";

    if (!isset($_SESSION['user_name'])) {
      $return_string .= "<returncode>0</returncode>\n";
      $return_string .= "<errormessage>You're not logged in!</errormessage>\n";
      return $return_string;
    }

    $from_webpage_id = getWebpageId($from_url);
    $to_webpage_id = getWebpageId($to_url);
    $from_webpagequery_id = 
        getPageParameterId($from_webpage_id, $from_url_param);
    $to_webpagequery_id = getPageParameterId($to_webpage_id, $to_url_param);

    $user_id = $_SESSION['user_id'];
    $query = "SELECT * FROM Link "
           . "WHERE from_webpagequery_id=$from_webpagequery_id "
           . "AND to_webpagequery_id=$to_webpagequery_id";
    $result = mysql_query($query);
    if (mysql_num_rows($result) == 0) {
      $return_string .= "<returncode>0</returncode>\n";
      $return_string .= "<errormessage>No such link!</errormessage>\n";
      return $return_string;
    }

    $query = "SELECT * FROM LinkRating WHERE rater_user_id=$user_id "
           . "AND from_webpagequery_id=$from_webpagequery_id "
           . "AND to_webpagequery_id=$to_webpagequery_id";
    $result = mysql_query($query);
    $rating = $up == 'true' ? 1 : -1;

    if (mysql_num_rows($result) == 0) {
      $query2 = "INSERT INTO LinkRating(rater_user_id, "
              . "from_webpagequery_id, to_webpagequery_id, rating) "
              . "VALUES($user_id, $from_webpagequery_id, "
              . "$to_webpagequery_id, $rating)";
      $result2 = mysql_query($query2);
    } else {
      $query2 = "UPDATE LinkRating SET rating=$rating "
              . "WHERE rater_user_id=$user_id "
              . "AND from_webpagequery_id=$from_webpagequery_id "
              . "AND to_webpagequery_id=$to_webpagequery_id";
      $result2 = mysql_query($query2);
    }
    $return_string .= "<returncode>1</returncode>";
    return $return_string;
  }
?>
